#!/usr/bin/env python
#encoding : utf-8

import os
from db import global_db
from base_handler import BaseHandler

class Query(BaseHandler):
	def __init__(self, req):
		super(Query, self).__init__(req)

	def process(self):
		self._output['data'] = {'staff_infos' : {}}
		self._output['template'] = 'staff_info.html'
		self._output['engine'] = 'jinja2'
		return True

class Display(BaseHandler):
	def __init__(self, req):
		super(Display, self).__init__(req)

	def process(self):
		popo = self._req.DICT_INPUT()['popo']
		group_name = self._req.DICT_INPUT()['group_name']
		gender = self._req.DICT_INPUT()['gender']
		print popo, group_name, gender
		if popo != '':
			sql = "select staff_info.popo, staff_info.name, staff_info.gender, staff_group.group_name, (select staff_info.name from staff_info where staff_info.popo = (select group_info.group_leader from group_info where group_info.group_name = staff_group.group_name) ) as group_leader from staff_info left join staff_group on staff_info.popo= staff_group.popo where staff_info.popo = '%s'; " % popo
		elif group_name != '' and gender != '':
			sql = "select staff_info.popo, staff_info.name, staff_info.gender, staff_group.group_name, (select staff_info.name from staff_info where staff_info.popo = (select group_info.group_leader from group_info where group_info.group_name = staff_group.group_name) ) as group_leader from staff_info left join staff_group on staff_info.popo= staff_group.popo where staff_group.group_name = '%s' and staff_info.gender = '%s'; " % (group_name, gender)
		elif group_name != '' and gender == '':
			sql = "select staff_info.popo, staff_info.name, staff_info.gender, staff_group.group_name, (select staff_info.name from staff_info where staff_info.popo = (select group_info.group_leader from group_info where group_info.group_name = staff_group.group_name) ) as group_leader from staff_info left join staff_group on staff_info.popo= staff_group.popo where staff_group.group_name = '%s'; " % group_name
		elif group_name == '' and gender != '':
			sql = "select staff_info.popo, staff_info.name, staff_info.gender, staff_group.group_name, (select staff_info.name from staff_info where staff_info.popo = (select group_info.group_leader from group_info where group_info.group_name = staff_group.group_name) ) as group_leader from staff_info left join staff_group on staff_info.popo= staff_group.popo where staff_info.gender= '%s'; " % gender
		else:
			sql = "select staff_info.popo, staff_info.name, staff_info.gender, staff_group.group_name, (select staff_info.name from staff_info where staff_info.popo = (select group_info.group_leader from group_info where group_info.group_name = staff_group.group_name) ) as group_leader from staff_info left join staff_group on staff_info.popo= staff_group.popo;" 

		self._output['data'] = {'staff_infos' : global_db.get_db('homework').query(sql)}
		self._output['template'] = 'staff_info.html'
		self._output['engine'] = 'jinja2'
		return True

class MMRatio(BaseHandler):
	def __init__(self, req):
		super(MMRatio, self).__init__(req)

	def process(self):
		popo = self._req.DICT_INPUT()['popo']
		group_name = self._req.DICT_INPUT()['group_name']
		gender = self._req.DICT_INPUT()['gender']
		print popo, group_name, gender

		sql = "select staff_group.group_name, count(case when staff_info.gender='F' then 1 else null end)/count(*)  as mm_ratio from staff_group left join staff_info on staff_group.popo = staff_info.popo group by staff_group.group_name with rollup;"
		self._output['data'] = {'staff_infos' : global_db.get_db('homework').query(sql)}
		self._output['template'] = 'staff_info.html'
		self._output['engine'] = 'jinja2'
		return True

class GroupSumm(BaseHandler):
	def __init__(self, req):
		super(GroupSumm, self).__init__(req)

	def process(self):
		popo = self._req.DICT_INPUT()['popo']
		group_name = self._req.DICT_INPUT()['group_name']
		gender = self._req.DICT_INPUT()['gender']
		print popo, group_name, gender

		sql = "select group_info.group_name, (select staff_info.name from staff_info where staff_info.popo= group_info.group_leader) as leader_name, count(*) as count from staff_group left join group_info on staff_group.group_name = group_info.group_name group by staff_group.group_name;"
		self._output['data'] = {'staff_infos' : global_db.get_db('homework').query(sql)}
		self._output['template'] = 'staff_info.html'
		self._output['engine'] = 'jinja2'
		return True


class StaticFile(BaseHandler):
	def __init__(self, req):
		super(StaticFile, self).__init__(req)

	def process(self):
		self._file_type = True
		return True

def query(req):
	return Query(req).run()

def display(req):
	return Display(req).run()

def static(req):
	return StaticFile(req).run()

def mmratio(req):
	return MMRatio(req).run()	

def groupsumm(req):
	return GroupSumm(req).run()
